package com.studymaster.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 
 *
 */
public class GetList {

	private int Column ;
	private String cellname="";
	private String[] cellarr=null;
	private List<Map<String,String>> list=null;
	public GetList()
	{
		
	}
	public void finish()
	{

	}
	/**
	 * 更新数据库
	 * @param sql
	 * @return
	 */
	public int getUpdate(String sql)
	{
		Connection connec=null;
		conn con=new conn();
		int result=-1;
		try {
			if(connec==null)
			{
				connec=con.getConn();
			}
			result=con.getUpdate(connec, sql);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			this.finish();
		}
		return result;
	}
	/**
	 * 从excel表中得到一个list链表
	 * @param path
	 * @param sheetIndex
	 * @return
	 * @throws IOException 
	 * @throws IOException
	 */
	public List<Map<String,String>> getList(String path,int sheetIndex) throws IOException
	{
		// System.out.println("hhh::::::::"+path);
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		FileInputStream istream = new FileInputStream(path);
		try {
			HSSFWorkbook work;

			work = new HSSFWorkbook(istream);

			HSSFSheet sheet = work.getSheetAt(sheetIndex);
			HSSFRow row = null;
			HSSFCell cell = null;
			List<String> listcell = new ArrayList<String>();
			Map<String, String> map = null;
			int index = 0;
			for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
				row = sheet.getRow(i);
				map = new HashMap<String, String>();
				index = 0;
				for (Iterator it = row.cellIterator(); it.hasNext();) {
					cell = (HSSFCell) it.next();
					if (i == 0) {
						map.put(cell.toString(), cell.toString());
						listcell.add(cell.toString());
					} else {
						map.put(listcell.get(index), cell.toString());
					}
					index++;
				}
				list.add(map);
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			istream.close();
		}
		this.list = list;
		return list;
	}
	/**
	 * 根据sql，从数据库表中读取一个链表
	 * @param sql
	 * @return
	 */
	public List<Map<String, String>> getList(String sql) {

		Connection connec=null;
		conn con=new conn();
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		String strName="";
		String arr[]=null;
		Statement stamet=null;
		ResultSet rs=null;
		try {
			if(connec==null)
			{connec = con.getConn();}
			stamet = connec.createStatement();
			rs = stamet.executeQuery(sql);
			this.Column = rs.getMetaData().getColumnCount();
			Map<String, String> map = null;
			map=new HashMap<String, String>();
			for(int j=0;j<Column;j++)
			{
				String cn=rs.getMetaData().getColumnName(j+1);
				//System.out.println(cn);
				map.put(cn, cn);
				if(!strName.equals(""))
					{strName=strName+","+cn;}
				else
					{strName=cn;}
			}	
			this.setCellname(strName);
			//System.out.println("列数：：："+Column+"列名窜:::"+strName);
			arr=strName.split(",");
			this.cellarr=arr;
			list.add(map);
			while (rs.next()) {
				map = new HashMap<String, String>();
				for (int i = 0; i < Column; i++) {	
					map.put(arr[i], rs.getString(arr[i])==null?"":rs.getString(arr[i]));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stamet != null)
				try {
					stamet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				con.closeConn(connec);
				con = null;
		}

		return list;
	}
	/**
	 * 返回列数
	 */
	public int getColumn() {
		return Column;
	}
	/**
	 * 返回列名
	 * @return
	 */
	public String getCellname() {
		return cellname;
	}
	public void setCellname(String cellname) {
		if(cellname!=null)
		{
			String[] arr=cellname.split(",");
			this.Column=arr.length;
			this.cellarr=arr;
		}
		this.cellname = cellname;
	}
	public String[] getCellarr() {
		return cellarr;
	}
	public void setCellarr(String[] cellarr) {
		this.cellarr = cellarr;
		String strName="";
		if(cellarr!=null)
		{
			this.Column=cellarr.length;
			for(int i=0;i<cellarr.length;i++)
			{
				if(!cellarr.equals(""))
				{strName=strName+","+cellarr[i];}
				else
					{strName=cellarr[i];}
				}
			}
			this.cellname=strName;
		}
	
	public String getStringFromMap(int rowIndex,int collIndex)
	{
		String str="";
		Map map=null;
		map=list.get(rowIndex);
		str=map.get(this.getCellarr()[collIndex]).toString();
		return str;
	}
}
